package com.dkd.sbtz.service.impl;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import com.dkd.common.annotation.DataScope;
import com.dkd.common.core.domain.TreeSelect;
import com.dkd.common.core.domain.entity.SysDept;
import com.dkd.common.exception.ServiceException;
import com.dkd.common.utils.DateUtils;
import com.dkd.common.utils.HashUtil;
import com.dkd.common.utils.StringUtils;
import com.dkd.common.utils.poi.ExcelUtil;
import com.dkd.common.utils.spring.SpringUtils;
import com.dkd.sbtz.domain.*;
import com.dkd.sbtz.domain.vo.*;
import com.dkd.sbtz.domain.vo.nodeImport.AssetImportRow;
import com.dkd.sbtz.domain.vo.nodeImport.SbtzExtImportDTO;
import com.dkd.sbtz.mapper.*;
import com.dkd.system.mapper.SysDeptMapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.math.NumberUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.stereotype.Service;
import com.dkd.sbtz.service.ISbtzService;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

/**
 * 设备台账Service业务层处理
 *
 * @author xzj
 * @date 2025-09-02
 */
@Slf4j
@Service
public class SbtzServiceImpl implements ISbtzService
{
    @Autowired
    private SbtzMapper sbtzMapper;

    @Autowired
    private SpareMapper spareMapper;

    @Autowired
    private SbtypeMapper  assetTypeEdgeMapper;

    @Autowired
    private SysDeptMapper deptMapper;

    @Autowired
    private AssetNodeSpareMapper assetNodeSpareMapper;

    @Autowired
    private SbtypeMapper sbtypeMapper;

    @Autowired
    private AssetAssessmentMapper assessmentMapper;
    /**
     * 查询设备台账
     *
     * @param id 设备台账主键
     * @return 设备台账
     */
    @Override
    public Sbtz selectSbtzById(Long id)
    {
        return sbtzMapper.selectSbtzById(id);
    }

    /**
     * 查询设备台账列表
     *
     * @param sbtz 设备台账
     * @return 设备台账
     */
    @Override
    public List<Sbtz> selectSbtzList(Sbtz sbtz)
    {
        return sbtzMapper.selectSbtzList(sbtz);
    }

    /**
     * 新增设备台账
     *
     * @param sbtz 设备台账
     * @return 结果
     */
    @Override
    public int insertSbtz(Sbtz sbtz)
    {
        sbtz.setCreateTime(DateUtils.getNowDate());
        return sbtzMapper.insertSbtz(sbtz);
    }

    /**
     * 修改设备台账
     *
     * @param sbtz 设备台账
     * @return 结果
     */
    @Override
    public int updateSbtz(Sbtz sbtz)
    {
        sbtz.setUpdateTime(DateUtils.getNowDate());
        return sbtzMapper.updateSbtz(sbtz);
    }

    /**
     * 批量删除设备台账
     *
     * @param ids 需要删除的设备台账主键
     * @return 结果
     */
    @Override
    public int deleteSbtzByIds(Long[] ids)
    {
        return sbtzMapper.deleteSbtzByIds(ids);
    }

    /**
     * 删除设备台账信息
     *
     * @param id 设备台账主键
     * @return 结果
     */
    @Override
    public int deleteSbtzById(Long id)
    {
        return sbtzMapper.deleteSbtzById(id);
    }

    // Jackson：用于把对象转成 JSON
    private static final com.fasterxml.jackson.databind.ObjectMapper M = new com.fasterxml.jackson.databind.ObjectMapper();


    // 计算“当前要插入子节点”的 ancestors（不含自己）
    private static String buildAncestors(String parentAncestors, Long parentId) {
        if (parentId == null || parentId == 0L) return "";           // 根的子：无祖先
        if (StringUtils.isBlank(parentAncestors)) return String.valueOf(parentId);
        return parentAncestors + "," + parentId;
    }


    @Override
    @Transactional(rollbackFor = Exception.class)
    public List<Long> createMachines(RouteCreateDTO dto, String operUser) {
        if (dto == null || dto.getLayers() == null || dto.getLayers().isEmpty()) {
            throw new ServiceException("没有要创建的母设备数据");
        }

        Long parentId = dto.getStartParentId();
        if (parentId == null || parentId <= 0) {
            throw new ServiceException("必须指定所属系统节点");
        }

        // 读取系统层信息
        Sbtz parent = sbtzMapper.getById(parentId);
        if (parent == null) {
            throw new ServiceException("系统节点不存在: " + parentId);
        }

        String parentPath = StringUtils.defaultIfBlank(parent.getPath(), "/");
        String parentAncestors = StringUtils.defaultString(parent.getAncestors());
        int parentLevel = parent.getLevel();

        List<Long> createdIds = new ArrayList<>();

        for (RouteCreateDTO.Layer layer : dto.getLayers()) {
            // 固定类型为 MACHINE
            String nodeType = "MACHINE";

            if (StringUtils.isBlank(layer.getName()) || StringUtils.isBlank(layer.getCode())) {
                throw new ServiceException("母设备名称与编码均不能为空");
            }

            // === 1) 创建 asset_node ===
            Sbtz node = new Sbtz();
            node.setName(layer.getName());
            node.setNodeType(nodeType);
            node.setCode(layer.getCode());
            node.setParentId(parentId);
            node.setLevel(parentLevel + 1);
            node.setAncestors(buildAncestors(parentAncestors, parentId));
            node.setPath(parentPath);
            node.setOrderNum(0);
            node.setDelFlag(0);
            node.setStatus(layer.getStatus() == null ? 0 : layer.getStatus());
            node.setRemark(layer.getRemark());
            node.setDeptId(layer.getDeptId());
            node.setSpecification(layer.getSpecification());
            node.setModel(layer.getModel());
            node.setMaterial(layer.getMaterial());
            node.setPower(layer.getPower());
            node.setBrand(layer.getBrand());
            node.setManufacturer(layer.getManufacturer());
            node.setIsSpecial(layer.getIsSpecial());
            node.setCreateBy(operUser);
            node.setUpdateBy(operUser);
            node.setCreateTime(new Date());
            node.setMokuai(layer.getMokuai());
            node.setArea(layer.getArea());
            node.setBeType(layer.getBeType());
            node.setDesignTemp(layer.getDesignTemp());
            node.setDesignPress(layer.getDesignPress());
            node.setMotorPower(layer.getMotorPower());
            node.setMixingType(layer.getMixingType());
            node.setUnit(layer.getUnit());
            node.setQuantity(layer.getQuantity());
            node.setAmount(layer.getAmount());

            sbtzMapper.insertNode(node);
            Long machineId = node.getId();

            // === 2) 更新 path ===
            String fullPath = safePath(parentPath) + machineId + "/";
            sbtzMapper.updatePath(machineId, fullPath);

            // === 3) 插入 asset_node_rel（系统 → 母设备） ===
            sbtzMapper.insertClosureForNewNode(parentId, machineId);

            // === 4) 插入 asset_node_spare（备件信息） ===
            if (layer.getSpareRelations() != null && !layer.getSpareRelations().isEmpty()) {
                List<AssetNodeSpare> list = layer.getSpareRelations().stream().map(sr -> {
                    AssetNodeSpare r = new AssetNodeSpare();
                    r.setAssetId(machineId);
                    r.setSpareId(sr.getSpareId());
                    r.setVendor(sr.getVendor());
                    r.setSpec(sr.getSpec());
                    r.setQtyNeed(sr.getQtyNeed() != null ? sr.getQtyNeed() : BigDecimal.ONE);
                    r.setIsPrimary(sr.getIsPrimary() != null ? sr.getIsPrimary() : 0);
                    r.setRemark(sr.getRemark());
                    return r;
                }).toList();
                assetNodeSpareMapper.batchInsert(list);
            }

            createdIds.add(machineId);
        }

        return createdIds;
    }




//    @Override
//    @Transactional(rollbackFor = Exception.class)
//    public List<Long> createRoute(RouteCreateDTO dto, String operUser) {
//        if (dto == null || dto.getLayers() == null || dto.getLayers().isEmpty()) {
//            throw new ServiceException("没有需要创建的层");
//        }
//
//        Long parentId = (dto.getStartParentId() == null ? 0L : dto.getStartParentId());
//        boolean reuse = Boolean.TRUE.equals(dto.getReuseCode());
//        String parentType;
//        String parentPath = "/";
//        String parentAncestors = "";
//        int parentLevel = 0;
//
//        if (parentId != 0L) {
//            Sbtz parent = sbtzMapper.getById(parentId);
//            if (parent == null) throw new ServiceException("父节点不存在：" + parentId);
//            parentType = parent.getNodeType();
//            parentPath = StringUtils.defaultIfBlank(parent.getPath(), "/");
//            parentAncestors = StringUtils.defaultString(parent.getAncestors());
//            parentLevel = parent.getLevel();
//        } else {
//            parentType = "ROOT";
//        }
//
//        List<Long> createdIds = new ArrayList<>();
//        List<Long> ancestorChain = new ArrayList<>();
//        if (parentId != 0L) ancestorChain.add(parentId);
//
//        // 若未选择系统节点但传递了 systemInfo，则先创建系统节点并用作母设备父节点
//        if ((parentId == 0L || "ROOT".equals(parentType)) && dto.getSystemInfo() != null) {
//            RouteCreateDTO.systemInfo sys = dto.getSystemInfo();
//            if (StringUtils.isNotBlank(sys.getName()) && StringUtils.isNotBlank(sys.getCode())) {
//                Sbtz sysNode = new Sbtz();
//                sysNode.setNodeType("SYSTEM");
//                sysNode.setName(sys.getName());
//                sysNode.setCode(ensureUniqueCodeCreate(sys.getCode(), false));
//                sysNode.setIsKey(sys.getIsKey());
//                sysNode.setParentId(0L);
//                sysNode.setLevel(1);
//                sysNode.setPath("/");          // 先给默认
//                sysNode.setAncestors("");      // 系统节点的 ancestors 为空
//                sysNode.setStatus(0);
//                sysNode.setDelFlag(0);
//                sysNode.setRemark("由母设备创建时自动生成系统节点");
//                sysNode.setCreateBy(operUser);
//                sysNode.setUpdateBy(operUser);
//                sysNode.setCreateTime(new Date());
//                sysNode.setUpdateTime(new Date());
//
//                sbtzMapper.insertNode(sysNode);
//
//                Long sysId = sysNode.getId();
//                // 更新系统节点 path
//                sbtzMapper.updatePath(sysId, "/" + sysId + "/");
//
//                // ✅ 关键：这里不要把 parentAncestors 设为 sysId
//                parentId        = sysId;
//                parentType      = "SYSTEM";
//                parentLevel     = 1;
//                parentPath      = "/" + sysId + "/";          // 父路径
//                parentAncestors = sysNode.getAncestors();     // ← 应该为 ""，而不是 sysId
//                createdIds.add(sysId);
//            }
//        }
//
//
//        // ✅ 正常层级创建逻辑
//        for (RouteCreateDTO.Layer layer : dto.getLayers()) {
//            if (StringUtils.isBlank(layer.getNodeType()) || StringUtils.isBlank(layer.getName())) {
//                throw new ServiceException("类型与名称为必填");
//            }
//
//            String childType = layer.getNodeType().trim().toUpperCase();
//            assertEdgeAllowed(parentType, childType);
//
//            if (reuse && StringUtils.isNotBlank(layer.getCode())) {
//                Sbtz existed = sbtzMapper.findOneByParentTypeCode(parentId, childType, layer.getCode().trim());
//                if (existed != null) {
//                    parentId = existed.getId();
//                    parentType = existed.getNodeType();
//                    parentLevel = existed.getLevel();
//                    String fullPath = safePath(parentPath) + parentId + "/";
//                    parentPath = fullPath;
//                    parentAncestors = appendAncestor(existed.getAncestors(), parentId);
//                    ancestorChain.add(parentId);
//                    continue;
//                }
//            }
//
//            String finalCode = ensureUniqueCodeCreate(StringUtils.trim(layer.getCode()), reuse);
//            Sbtz node = new Sbtz();
//            node.setName(layer.getName());
//            node.setNodeType(childType);
//            node.setCode(finalCode);
//            node.setParentId(parentId);
//            node.setLevel(parentLevel + 1);
//            node.setAncestors(buildAncestors(parentAncestors, parentId));
//            node.setPath(parentPath);
//            node.setOrderNum(0);
//            node.setStatus(layer.getStatus());
//            node.setDelFlag(0);
//            node.setRemark(layer.getRemark());
//            node.setCreateBy(operUser);
//            node.setUpdateBy(operUser);
//            node.setCreateTime(new Date());
//            node.setSpecification(layer.getSpecification());
//            node.setModel(layer.getModel());
//            node.setMaterial(layer.getMaterial());
//            node.setPower(layer.getPower());
//            node.setBrand(layer.getBrand());
//            node.setManufacturer(layer.getManufacturer());
//            node.setIsSpecial(layer.getIsSpecial());
//            node.setDeptId(layer.getDeptId());
//            node.setMokuai(layer.getMokuai());
//            node.setArea(layer.getArea());
//            node.setBeType(layer.getBeType());
//            node.setDesignTemp(layer.getDesignTemp());
//            node.setDesignPress(layer.getDesignPress());
//            node.setMotorPower(layer.getMotorPower());
//            node.setMixingType(layer.getMixingType());
//            node.setUnit(layer.getUnit());
//            node.setQuantity(layer.getQuantity());
//            node.setAmount(layer.getAmount());
//
//            sbtzMapper.insertNode(node);
//            Long id = node.getId();
//            String fullPath = safePath(parentPath) + id + "/";
//            sbtzMapper.updatePath(id, fullPath);
//            sbtzMapper.insertClosureForNewNode(parentId, id);
//
//            // === 备件逻辑（原样保留） ===
//            if (layer.getSpareRelations() != null && !layer.getSpareRelations().isEmpty()) {
//                List<AssetNodeSpare> list = layer.getSpareRelations().stream().map(sr -> {
//                    AssetNodeSpare r = new AssetNodeSpare();
//                    r.setAssetId(id);
//                    r.setSpareId(sr.getSpareId());
//                    r.setVendor(sr.getVendor());
//                    r.setSpec(sr.getSpec());
//                    r.setQtyNeed(sr.getQtyNeed() != null ? sr.getQtyNeed() : BigDecimal.ONE);
//                    r.setIsPrimary(sr.getIsPrimary() != null ? sr.getIsPrimary() : 0);
//                    r.setRemark(sr.getRemark());
//                    return r;
//                }).toList();
//                assetNodeSpareMapper.batchInsert(list);
//            }
//
//            // === 自定义备件逻辑（原样保留） ===
//            if (layer.getSpareCustom() != null && !layer.getSpareCustom().isEmpty()) {
//                handleCustomSpares(
//                        layer.getSpareCustom(),
//                        id, operUser,
//                        layer.getSpecification(),
//                        layer.getModel(),
//                        layer.getMaterial()
//                );
//            }
//
//            if (layer.getDeptId() != null) {
//                changeUsage(id, layer.getStatus(), (long) layer.getDeptId(), operUser, "");
//            }
//            if (layer.getSpareCustom() != null && StringUtils.isNotBlank(layer.getBeType())) {
//                Long minId = sbtzMapper.selectMinIdByBeType(layer.getBeType());
//                if (minId != null) {
//                    sbtzMapper.updateBeTypeFirst(minId, 1);
//                } else {
//                    sbtzMapper.updateBeTypeFirst(id, 1);
//                }
//            }
//
//            parentId = id;
//            parentType = childType;
//            parentLevel = node.getLevel();
//            parentPath = fullPath;
//            parentAncestors = node.getAncestors();
//            createdIds.add(id);
//        }
//        return createdIds;
//    }
//


    // 生成唯一备件编码（SPyyyyMMddNNN）
    private String nextSpareCode() {
        String prefix = "SP" + LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        String max = spareMapper.selectMaxCodeLike(prefix);
        int seq = 1;
        if (StringUtils.isNotBlank(max) && max.length() > prefix.length()) {
            String tail = max.substring(prefix.length());
            seq = NumberUtils.toInt(tail, 0) + 1;
        }
        return prefix + String.format("%03d", seq);
    }


    public void changeUsage(Long assetId, Integer status, Long deptId, String operator,String reason) {
        if (assetId == null || status == null) {
            throw new ServiceException("assetId/status 不能为空");
        }
        if (status < 0 || status > 4) {
            throw new ServiceException("非法的设备状态");
        }

        final LocalDateTime now = LocalDateTime.now();
        AssetNodeUsage open = sbtzMapper.selectOpenByAsset(assetId); // 当前未闭合记录（可能为 null）

        if (status == 0) { // 使用：必须选部门
            if (deptId == null || deptId <= 0) {
                throw new ServiceException("启用状态必须选择部门");
            }
            SysDept dept = deptMapper.selectDeptById(deptId);
            if (dept == null || "2".equals(dept.getStatus())) {
                throw new ServiceException("部门不存在或已停用");
            }

            // 幂等：如果已是“使用中 & 部门相同”，直接返回
            if (open != null && open.getStatus() == 0 && deptId.equals(open.getDeptId())) {
                return;
            }

            // 关闭旧打开记录（无论旧记录是什么状态）
            if (open != null) {
                sbtzMapper.closeOpenByAsset(assetId, now);
            }

            // 插入新的“使用”记录
            AssetNodeUsage row = new AssetNodeUsage();
            row.setAssetId(assetId);
            row.setStatus(status);
            row.setDeptId(deptId);
            row.setDeptName(dept.getDeptName() == null ? "" : dept.getDeptName());
            row.setStartTs(now);
            row.setEndTs(null);
            row.setReason(reason);
            row.setCreatedBy(operator);

            // 唯一约束下可能出现并发/重复，采用 insertIgnore 风格保证不抛错
            sbtzMapper.insertIgnore(row);
            return;
        }

        // 非使用状态：部门必须为空（保存为 0 / ""）
        Long deptIdNorm = 0L;
        String deptNameNorm = "";

        // 幂等：如果已有打开记录且状态相同且 dept=0，则直接返回
        if (open != null && open.getStatus() == status &&
                (open.getDeptId() == null || open.getDeptId() == 0L)) {
            return;
        }

        // 关闭旧打开记录
        if (open != null) {
            sbtzMapper.closeOpenByAsset(assetId, now);
        }

        // 插入新的“非使用”记录
        AssetNodeUsage row = new AssetNodeUsage();
        row.setAssetId(assetId);
        row.setStatus(status);
        row.setDeptId(deptIdNorm);
        row.setDeptName(deptNameNorm);
        row.setStartTs(now);
        row.setEndTs(null);
        row.setReason(reason == null ? "" : reason);
        row.setCreatedBy(operator);

        sbtzMapper.insertIgnore(row);
    }



    private String ensureUniqueCodeCreate(String code, boolean reuse) {
        if (StringUtils.isBlank(code)) return null; // 允许无编码
        if (sbtzMapper.existsCode(code) == 0) return code;
        if (!reuse) throw new ServiceException("编码已存在：" + code);
        String base = code; int i = 1; String candidate = code;
        while (sbtzMapper.existsCode(candidate) > 0) candidate = base + "-" + i++;
        return candidate;
    }


    /** 全量 code -> 名称 的映射（缓存） */
    @Cacheable(value = "assetTypeNameMap")
    public Map<String, String> getTypeNameMap() {
        List<Sbtype> all = sbtypeMapper.selectSbtypeList(new Sbtype());
        return all.stream()
                .filter(t -> t.getTypeCode() != null)
                .collect(Collectors.toMap(Sbtype::getTypeCode, Sbtype::getDisplayName, (a,b)->a));
    }

    /** 断言：带中文名 */
    public void assertEdgeAllowed(String parentType, String childType) {
        if (sbtypeMapper.existsEdge(parentType, childType) > 0) return;

        Map<String, String> nameMap = getTypeNameMap();
        String parentName = nameMap.getOrDefault(parentType, parentType);
        String childName  = nameMap.getOrDefault(childType,  childType);
        throw new ServiceException("不允许的父子类型：" + parentName + "（" + parentType + "） → " + childName + "（" + childType + "）");
    }




    private String joinAncestors(String parentAncestors, List<Long> chain) {
        if (chain == null || chain.isEmpty()) return StringUtils.defaultString(parentAncestors);
        StringBuilder sb = new StringBuilder(StringUtils.defaultString(parentAncestors));
        for (Long id : chain) {
            if (sb.length() > 0 && sb.charAt(sb.length()-1) != ',') sb.append(',');
            sb.append(id);
        }
        return sb.toString();
    }

    private String appendAncestor(String oldAnc, Long self) {
        return StringUtils.isBlank(oldAnc) ? String.valueOf(self) : oldAnc + "," + self;
    }

    private String safePath(String p) { return StringUtils.defaultIfBlank(p, "/"); }
    /*
        完成前端新增层级关系的代码
    */



    private String toJson(Object o) {
        try { return M.writerWithDefaultPrettyPrinter().writeValueAsString(o); }
        catch (Exception e) { return String.valueOf(o); }
    }
    private int size(RouteCreateDTO dto){ return dto==null||dto.getLayers()==null?0:dto.getLayers().size(); }


    /*懒加载父节点选择器*/
    @Override
//    public List<NodeOptionVO> children(Long parentId) {
//        Long pid = (parentId == null ? 0L : parentId);
//        List<Sbtz> list = sbtzMapper.selectByParentId(pid);
//        List<NodeOptionVO> out = new ArrayList<>(list.size());
//        for (Sbtz n : list) {
//            boolean hasChild = sbtzMapper.existsChild(n.getId()) > 0;
//            String label = n.getName() + (n.getCode() != null ? "（" + n.getCode() + "）" : "");
//            out.add(new NodeOptionVO(n.getId(), label, !hasChild)); // leaf = !hasChild
//        }
//        return out;
//    }
    public List<NodeOptionVO> children(Long parentId) {
        Long pid = (parentId == null ? 0L : parentId);
        List<Sbtz> list = sbtzMapper.selectByParentId(pid);
        List<NodeOptionVO> out = new ArrayList<>(list.size());
        for (Sbtz n : list) {
            boolean hasChild = sbtzMapper.existsChild(n.getId()) > 0;
            String label = n.getName() + (n.getCode() != null ? "（" + n.getCode() + "）" : "");
            // ✨ 把 nodeType 一起返回；懒加载需要的是 leaf 字段（无子=leaf=true）
            out.add(new NodeOptionVO(
                    n.getId(),
                    label,
                    !hasChild,           // leaf
                    n.getNodeType()      // ✨ nodeType
            ));
        }
        return out;
    }



    private static <T> T nvl(T a, T b) { return a != null ? a : b; }


    /*查询数据*/
//    @Override
//    @Transactional(readOnly = true)
//    public List<AssetNodeListVO> selectSbtzListVO(Sbtz sbtz) {
//        return sbtzMapper.selectSbtzListVO(sbtz);
//    }

    @Override
    @Transactional(readOnly = true)
    public List<Sbtz> selectSbtzListVO(Sbtz cond) {
        // PageHelper 已在 Controller startPage + orderBy
        List<Sbtz> pageNodes = sbtzMapper.selectNodePage(cond);
        if (pageNodes == null || pageNodes.isEmpty()) return pageNodes;

        // 这页涉及到的设备ID
        List<Long> ids = pageNodes.stream().map(Sbtz::getId).toList();


        List<AssetAssessment> ments = assessmentMapper.selectNodeMentInfo(ids);
        // 评估信息分组
        Map<Long, List<AssetAssessment>> mentGrouped = ments.stream()
                .collect(Collectors.groupingBy(AssetAssessment::getAssetId));

        // 一次性把备件行拉出来并按 assetId 分组
//        List<NodeSpareRow> rows = sbtzMapper.selectNodeSpares(ids);
//        Map<Long, List<Spare>> grouped = rows.stream()
//                .collect(Collectors.groupingBy(
//                        NodeSpareRow::getAssetId,
//                        Collectors.mapping(SbtzServiceImpl::toSpare, Collectors.toList())
//                ));

        // 回填
        for (Sbtz n : pageNodes) {
//            List<Spare> list = grouped.get(n.getId());
//            n.setSpareInfos(list == null ? Collections.emptyList() : list);

            List<AssetAssessment> assessments = mentGrouped.get(n.getId());
            n.setAssessmentInfos(assessments == null ? Collections.emptyList() : assessments);

        }
        return pageNodes;
    }

    private static Spare toSpare(NodeSpareRow r) {
        Spare s = new Spare();
        s.setId(r.getId());
        s.setCode(r.getCode());
        s.setName(r.getName());
        s.setStockTotal(r.getStockTotal());
        s.setStockAvailable(r.getStockAvailable());
        s.setIsPrimary(r.getIsPrimary());
        s.setRelRemark(r.getRelRemark());
        s.setQtyNeed(r.getQtyNeed());
        s.setRelVendor(r.getRelVendor());
        s.setRelSpec(r.getRelSpec());
        s.setAssetId(r.getAssetId());
        // 如需把关系字段透给前端，可扩展 Spare 或做一个 SpareVO：
        // s.setStatus(...) / 扩展字段……
        return s;
    }

    /*查询数据*/
    @Override
    @Transactional(readOnly = true)
    public List<AssetNodeListVO> selectSearchData(Sbtz sbtz) {
        return sbtzMapper.selectSearchData(sbtz);
    }


    /*导出数据*/
    @Override
    @Transactional(readOnly = true)
    public List<AssetNodeExportVO> selectForExport(Sbtz sbtz) {
        return sbtzMapper.selectForExport(sbtz);
    }


    @Override
    @Transactional(readOnly = true)
    public List<ExportWithParentVO> selectChainRowsForExport(Sbtz sbtz) {
        return sbtzMapper.selectChainRowsForExport(sbtz);
    }

    /**
     * 删除节点及其全部子孙节点（硬删除）
     * @return 实际删除的记录数
     */
//    @Override
//    @Transactional(rollbackFor = Exception.class)
//    public int deleteSubtree(Long nodeId, String operUser) {
//        // 可选：行级锁，避免并发重家/删除冲突
//        Sbtz cur = sbtzMapper.lockById(nodeId); // SELECT ... FOR UPDATE
//        if (cur == null) throw new ServiceException("节点不存在: " + nodeId);
//
//        // 查询所有子孙
//        List<Long> descendantIds = sbtzMapper.selectDescendantIds(nodeId);
//
//        int affected = 0;
//        if (!descendantIds.isEmpty()) {
//            affected += sbtzMapper.deleteNodesByIds(descendantIds);
//        }
//        affected += sbtzMapper.deleteNodeById(nodeId);
//
//        // 可选：记录审计日志
//        // auditLog.info("deleteSubtree", operUser, nodeId, descendantIds);
//
//        return affected;
//    }
    @Override
    @Transactional(rollbackFor = Exception.class)
    public int deleteSubtree(Long nodeId, String operUser) {
        // 可选：行级锁，避免并发移动/删除冲突
        Sbtz cur = sbtzMapper.lockById(nodeId);
        if (cur == null) throw new ServiceException("节点不存在: " + nodeId);

        int affected = 0;

        // 1) 删除全部子孙节点（不含自己）
        //    由于你已对 asset_node_rel、asset_node_spare 配了 ON DELETE CASCADE，
        //    这些子孙相关的闭包行/备件行会自动被清理。
        affected += sbtzMapper.deleteChildrenByAncestor(nodeId);

        // 2) 删除自己
        affected += sbtzMapper.deleteNodeById(nodeId);

        // 可选：记录审计日志
        // auditLog.info("deleteSubtree", operUser, nodeId, affected);

        return affected;
    }


    public static <T, R> R mapOrElse(T obj, java.util.function.Function<T, R> fn, R defVal) {
        if (obj == null) return defVal;
        R v = fn.apply(obj);
        return v != null ? v : defVal;
    }

    /*修改节点所属关系*/
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Long rehomeByRecreate(RehomeByRecreateDTO req, String operUser) {
        Long nodeId      = req.getNodeId();
        Long newParentId = (req.getNewParentId() == null ? 0L : req.getNewParentId());

        Sbtz cur = sbtzMapper.getById(nodeId);
        if (cur == null) throw new ServiceException("节点不存在: " + nodeId);

        Long oldParentId = (cur.getParentId() == null ? 0L : cur.getParentId());
        boolean moving = !Objects.equals(newParentId, oldParentId);

        String parentType = "ROOT";
        Sbtz newParent = null;
        if (newParentId != 0L) {
            newParent = sbtzMapper.getById(newParentId);
            if (newParent == null) throw new ServiceException("新父节点不存在: " + newParentId);
            parentType = newParent.getNodeType();
        }

        RehomeByRecreateDTO.OverrideLayer ov = req.getOverride();
        String newType = (ov != null && StringUtils.isNotBlank(ov.getNodeType()))
                ? ov.getNodeType().trim()
                : cur.getNodeType();

        if (moving || !Objects.equals(newType, cur.getNodeType())) {
            if (assetTypeEdgeMapper.existsEdge(parentType, newType) <= 0) {
                throw new ServiceException("不允许的父子类型: " + parentType + " → " + newType);
            }
        }

        String specification = (ov != null && ov.getSpecification() != null) ? ov.getSpecification().trim() : "";
        String model = (ov != null && ov.getModel() != null) ? ov.getModel().trim() : "";
        String material = (ov != null && ov.getMaterial() != null) ? ov.getMaterial().trim() : "";
        String power = (ov != null && ov.getPower() != null) ? ov.getPower().trim() : "";
        String brand = (ov != null && ov.getBrand() != null) ? ov.getBrand().trim() : "";
        String manufacturer = (ov != null && ov.getManufacturer() != null) ? ov.getManufacturer().trim() : "";
        Integer isSpecial = mapOrElse(ov, x -> x.getIsSpecial(), 0);
        Integer deptId    = (ov != null && ov.getDeptId() != null) ? ov.getDeptId() : 0;
        Integer status    = mapOrElse(ov, x -> x.getStatus(), 0);
        String mokuai = (ov != null && ov.getMokuai() != null) ? ov.getMokuai().trim() : "";
        String area = (ov != null && ov.getArea() != null) ? ov.getArea().trim() : "";
        String beType = (ov != null && ov.getBeType() != null) ? ov.getBeType().trim() : "";
        String designTemp = (ov != null && ov.getDesignTemp() != null) ? ov.getDesignTemp().trim() : "";
        String designPress = (ov != null && ov.getDesignPress() != null) ? ov.getDesignPress().trim() : "";
        String motorPower = (ov != null && ov.getMotorPower() != null) ? ov.getMotorPower().trim() : "";
        String mixingType = (ov != null && ov.getMixingType() != null) ? ov.getMixingType().trim() : "";
        String unit = (ov != null && ov.getUnit() != null) ? ov.getUnit().trim() : "";
        Integer quantity = (ov != null && ov.getQuantity() != null) ? ov.getQuantity() : 0;
        BigDecimal amount = (ov != null && ov.getAmount() != null) ? ov.getAmount() : BigDecimal.ZERO;


        // ✅ 把“本次是否要改备件”归一化：null=不改，[] = 清空， [ids...] = 替换
        List<Long> spareIdsToSet = normalizeSpareIds(ov);

        SbtzPatchReq upData = new SbtzPatchReq();
        upData.setId(nodeId);
        upData.setUpdateBy(operUser);
        upData.setSpecification(specification);
        upData.setModel(model);
        upData.setMaterial(material);
        upData.setPower(power);
        upData.setBrand(brand);
        upData.setManufacturer(manufacturer);
        upData.setIsSpecial(isSpecial);
        upData.setDeptId(deptId);
        upData.setStatus(status);
        upData.setUpdateTime(new Date());
        upData.setBeType(beType);
        upData.setMokuai(mokuai);
        upData.setArea(area);
        upData.setDesignTemp(designTemp);
        upData.setDesignPress(designPress);
        upData.setMotorPower(motorPower);
        upData.setMixingType(mixingType);
        upData.setUnit(unit);
        upData.setQuantity(quantity);
        upData.setAmount(amount);

        if(deptId != null){
            changeUsage(nodeId, status, (long)deptId, operUser,"");
        }

        // ===== A) 不搬家：只更改自身字段/编码/备注，最后用 spareIdsToSet 统一替换 =====
        if (!moving) {
            String newName = (ov != null && StringUtils.isNotBlank(ov.getName()))
                    ? ov.getName().trim() : cur.getName();

            String newCode;
            if (Boolean.TRUE.equals(req.getKeepCode())) {
                newCode = (ov != null && StringUtils.isNotBlank(ov.getCode()))
                        ? ensureUniqueCode(ov.getCode().trim(), true, nodeId)
                        : cur.getCode();
            } else {
                String wished = (ov == null ? null : StringUtils.trimToNull(ov.getCode()));
                newCode = (wished == null) ? null : ensureUniqueCode(wished, false, nodeId);
            }

            String newRemark = (ov != null && ov.getRemark() != null) ? ov.getRemark() : cur.getRemark();

            upData.setName(newName);
            upData.setCode(newCode);
            upData.setNodeType(newType);
            upData.setRemark(newRemark);
            sbtzMapper.updateByIdSelective(upData);

            // ✅ 仅当 spareIdsToSet != null 时才“清空并重建”，否则保持原状
//            replaceNodeSpareLinks(nodeId, spareIdsToSet, newRemark);


            if (ov.getSpareRelations() != null) {
                assetNodeSpareMapper.deleteByAssetId(nodeId);
                if (!ov.getSpareRelations().isEmpty()) {
                    List<AssetNodeSpare> list = new ArrayList<>();
                    for (RouteCreateDTO.SpareRelation rel : ov.getSpareRelations()) {
                        AssetNodeSpare entity = new AssetNodeSpare();
                        entity.setAssetId(nodeId);
                        entity.setSpareId(rel.getSpareId());
                        entity.setVendor(rel.getVendor());
                        entity.setSpec(rel.getSpec());
                        entity.setQtyNeed(rel.getQtyNeed());
                        entity.setIsPrimary(Boolean.TRUE.equals(rel.getIsPrimary()) ? 1 : 0);
                        entity.setRemark(rel.getRemark());
                        list.add(entity);
                    }
                    assetNodeSpareMapper.batchInsert(list);
                }
            }

            if (ov != null && ov.getSpareCustom() != null && !ov.getSpareCustom().isEmpty()) {
                handleCustomSpares(
                        ov.getSpareCustom(),
                        nodeId,
                        operUser,
                        ov.getSpecification(),
                        ov.getModel(),
                        ov.getMaterial()
                );
            }


            return nodeId;
        }

        // ===== B) 搬家：你的原有逻辑不变，最后也用 spareIdsToSet 统一替换 =====
        if (newParentId.equals(nodeId)) throw new ServiceException("新父节点不能是自己");

        sbtzMapper.deleteChildrenByAncestor(nodeId);

        String newName = (ov != null && StringUtils.isNotBlank(ov.getName()))
                ? ov.getName().trim() : cur.getName();

        String newCode;
        if (Boolean.TRUE.equals(req.getKeepCode())) {
            newCode = (ov != null && StringUtils.isNotBlank(ov.getCode()))
                    ? ensureUniqueCode(ov.getCode().trim(), true, nodeId)
                    : cur.getCode();
        } else {
            String wished = (ov == null ? null : StringUtils.trimToNull(ov.getCode()));
            newCode = (wished == null) ? null : ensureUniqueCode(wished, false, nodeId);
        }

        String newRemark = (ov != null && ov.getRemark() != null) ? ov.getRemark() : cur.getRemark();

        upData.setName(newName);
        upData.setCode(newCode);
        upData.setNodeType(newType);
        upData.setRemark(newRemark);
        sbtzMapper.updateByIdSelective(upData);

        // ✅ 仅当显式传了 spareIds/spareId 时才替换；否则不动原有关联
        replaceNodeSpareLinks(nodeId, spareIdsToSet, newRemark);

        sbtzMapper.deleteSelfExternalClosure(nodeId);
        sbtzMapper.insertClosureForExistingNode(newParentId, nodeId);

        String ancestors = (newParentId == 0L) ? ""
                : (StringUtils.isBlank(newParent.getAncestors())
                ? String.valueOf(newParentId)
                : newParent.getAncestors() + "," + newParentId);

        String path  = (newParentId == 0L) ? ("/" + nodeId + "/") : (newParent.getPath() + nodeId + "/");
        int    level = (newParentId == 0L) ? 1 : newParent.getLevel() + 1;

        sbtzMapper.updateSelfMetaAfterRehome(nodeId, newParentId, ancestors, path, level, operUser);
        return nodeId;
    }



    /**
     * 通用自定义备件添加逻辑
     */
    private void handleCustomSpares(
            List<RouteCreateDTO.SpareCustom> customList,
            Long assetId,
            String operUser,
            String specification,
            String model,
            String material) {

        if (customList == null || customList.isEmpty()) return;

        for (RouteCreateDTO.SpareCustom custom : customList) {
            // === 1. 新增备件表 ===
            Spare spare = new Spare();
            spare.setName(StringUtils.defaultString(custom.getName()));
            spare.setStatus(0);
            spare.setRemark("由自定义备件生成");
            spare.setCreateBy(operUser);
            spare.setUpdateBy(operUser);
            spare.setCreateTime(new Date());
            spare.setUpdateTime(new Date());
            spare.setSpecification(specification);
            spare.setModel(model);
            spare.setMaterial(material);
            spare.setAmount(custom.getAmount());
            spare.setGrade("");
            spare.setSpareClass(custom.getSpareClass());

            String codeFromFront = StringUtils.trimToNull(custom.getCode());
            for (int attempt = 0; attempt < 6; attempt++) {
                String codeToUse = codeFromFront;
                if (StringUtils.isBlank(codeToUse)) codeToUse = nextSpareCode();
                spare.setCode(codeToUse);
                try {
                    spareMapper.insertSpare(spare);
                    break;
                } catch (DuplicateKeyException dup) {
                    codeFromFront = null;
                    if (attempt == 5) {
                        throw new ServiceException("生成备件编码失败，请重试");
                    }
                }
            }
            Long spareId = spare.getId();
            if(spareId == null){
                spareId = spareMapper.selectIdByCode(custom.getCode());
            }

//            Long spareId = spareMapper.selectIdByCode(custom.getCode());
//            if(spareId == null){
//                String codeFromFront = StringUtils.trimToNull(custom.getCode());
//                for (int attempt = 0; attempt < 6; attempt++) {
//                    String codeToUse = codeFromFront;
//                    if (StringUtils.isBlank(codeToUse)) codeToUse = nextSpareCode();
//                    spare.setCode(codeToUse);
//                    try {
//                        spareMapper.insertSpare(spare);
//                        break;
//                    } catch (DuplicateKeyException dup) {
//                        codeFromFront = null;
//                        if (attempt == 5) {
//                            throw new ServiceException("生成备件编码失败，请重试");
//                        }
//                    }
//                }
//                spareId = spare.getId();
//            }

            final Long finalSpareId = spareId;
            if (finalSpareId == null || finalSpareId == 0) {
                throw new ServiceException("备件插入失败，未找到对应 ID，code=" + spare.getCode());
            }

            // === 2. 插入变体表 ===
            if (custom.getSpareDetails() != null && !custom.getSpareDetails().isEmpty()) {
                List<AssetSpareVariant> variants = custom.getSpareDetails().stream().map(d -> {
                    AssetSpareVariant v = new AssetSpareVariant();
                    v.setSpareId(finalSpareId);
                    v.setVendor(StringUtils.defaultString(d.getVendor()));
                    v.setSpec(StringUtils.defaultString(d.getSpec()));
                    v.setStatus(0);
                    v.setCreateBy(operUser);
                    return v;
                }).toList();
                spareMapper.batchInsertOrUpdateVariants(variants);

                // === 3. 绑定节点关系 ===
                RouteCreateDTO.SpareDetail pick = custom.getSpareDetails().get(0);
                AssetNodeSpare link = new AssetNodeSpare();
                link.setAssetId(assetId);
                link.setSpareId(finalSpareId);
                link.setVendor(StringUtils.defaultString(pick.getVendor()));
                link.setSpec(StringUtils.defaultString(pick.getSpec()));
                link.setQtyNeed(BigDecimal.ONE);
                link.setIsPrimary(0);
                link.setRemark("自定义备件自动关联");
                assetNodeSpareMapper.batchInsert(Collections.singletonList(link));
            }
        }
    }


    /** 保障编码唯一；allowReuse=true 允许本记录保留原码（排除自身） */
    private String ensureUniqueCode(String code, boolean allowReuse, Long selfId) {
        if (StringUtils.isBlank(code)) return null;
        int cnt = sbtzMapper.countCodeExcludeSelf(code, selfId);
        if (cnt == 0) return code;
        if (!allowReuse) throw new ServiceException("编码已存在：" + code);
        String base = code; int i = 1; String c = code;
        while (sbtzMapper.countCodeExcludeSelf(c, selfId) > 0) c = base + "-" + i++;
        return c;
    }



    /** 归一化：优先用 spareIds；否则用 spareId；返回 null 表示“前端没传——不改动现有关联” */
    private List<Long> normalizeSpareIds(RehomeByRecreateDTO.OverrideLayer ov) {
        if (ov == null) return null;
        if (ov.getSpareIds() != null) {
            return ov.getSpareIds().stream()
                    .filter(Objects::nonNull)
                    .map(Long::valueOf)
                    .filter(x -> x > 0L)
                    .distinct()
                    .toList();
        }
        if (ov.getSpareId() != null && ov.getSpareId() > 0) {
            return Collections.singletonList(ov.getSpareId());
        }
        // 都没传：返回 null，表示“不改变现状”
        return null;
    }

    /** 替换：清空后按给定集合重建；空列表=清空，null=不处理 */
    private void replaceNodeSpareLinks(Long nodeId, List<Long> spareIds, String remark) {
        if (spareIds == null) return;                 // null => 不动
        spareMapper.deleteNodeSpareLinks(nodeId);      // 显式传了 => 先清
        if (!spareIds.isEmpty()) {
            spareMapper.batchInsertNodeSpareLinks(nodeId, spareIds, remark);
        }
    }



    /**
     * 仅删除子树（不含自身），需要时也可以暴露给前端
     */
    @Transactional(rollbackFor = Exception.class)
    public int deleteChildren(Long nodeId, String operUser) {
        Sbtz cur = sbtzMapper.lockById(nodeId);
        if (cur == null) throw new ServiceException("节点不存在: " + nodeId);

        List<Long> descendantIds = sbtzMapper.selectDescendantIds(nodeId);
        if (descendantIds.isEmpty()) return 0;
        // 这里如果 selectDescendantIds 返回的是“所有后代”，会包含多层级，满足需要
        int affected = sbtzMapper.deleteNodesByIds(descendantIds);

        // auditLog.info("deleteChildren", operUser, nodeId, descendantIds);
        return affected;
    }


    /*返回类似部门一样的数列表选择*/
    @Override
    public List<TreeSelect> selectAssetTreeList(Sbtz sbtz) {
        // 这里不要 startPage()
        List<Sbtz> list = sbtzMapper.selectAssetTreeList(sbtz);
        List<Sbtz> tree = buildSbtzTree(list);
        return tree.stream().map(this::toTreeSelect).collect(Collectors.toList());
    }

    /** 把平铺列表组树：O(n)，并初始化 children，最后做统一排序 */
    private List<Sbtz> buildSbtzTree(List<Sbtz> list) {
        if (list == null || list.isEmpty()) return Collections.emptyList();

        // 先放进 map，并初始化 children，避免 NPE
        Map<Long, Sbtz> map = new HashMap<>(list.size() * 4 / 3 + 1);
        for (Sbtz n : list) {
            n.setChildren(new ArrayList<>());  // 保证非 null
            map.put(n.getId(), n);
        }

        List<Sbtz> roots = new ArrayList<>();
        for (Sbtz n : list) {
            Long pid = n.getParentId() == null ? 0L : n.getParentId();
            Sbtz parent = map.get(pid);
            if (parent == null) {
                // 没有父或父不在当前集合中 → 视为根
                roots.add(n);
            } else {
                parent.getChildren().add(n);
            }
        }

        // 统一排序：orderNum -> name（按需修改）
        Comparator<Sbtz> cmp = Comparator
                .comparing((Sbtz x) -> x.getOrderNum() == null ? 0 : x.getOrderNum())
                .thenComparing(x -> x.getName() == null ? "" : x.getName());
        sortRecursively(roots, cmp);

        return roots;
    }

    private void sortRecursively(List<Sbtz> nodes, Comparator<Sbtz> cmp) {
        nodes.sort(cmp);
        for (Sbtz n : nodes) {
            if (n.getChildren() != null && !n.getChildren().isEmpty()) {
                sortRecursively(n.getChildren(), cmp);
            }
        }
    }

    /** Sbtz → RuoYi 的 TreeSelect */
    private TreeSelect toTreeSelect(Sbtz n) {
        TreeSelect ts = new TreeSelect();
        ts.setId(n.getId());
        ts.setLabel(buildLabel(n));
        if (n.getChildren() != null && !n.getChildren().isEmpty()) {
            ts.setChildren(n.getChildren().stream().map(this::toTreeSelect).collect(Collectors.toList()));
        }
        return ts;
    }

    private String buildLabel(Sbtz n) {
        String name = n.getName() == null ? "" : n.getName();
        String code = n.getCode();
        return (code == null || code.isEmpty()) ? name : name + "(" + code + ")";
    }
    /*返回类似部门一样的数列表选择完毕*/




    @Override
    public List<AssetNodeUsage> selectNodeUsageList(AssetNodeUsage nodeUsage){
        return sbtzMapper.selectDeptUsageList(nodeUsage);
    }

    @Override
    public List<AssetNodeUsage> selectNodeUsageById(Long nodeId) {
        if (nodeId == null) return Collections.emptyList();
        AssetNodeUsage q = new AssetNodeUsage();
        q.setAssetId(nodeId);            // 把路径里的 id 塞到查询对象
        // 需要的话可在这里塞 q.setOpenFlag(1) 只看占用中
        return sbtzMapper.selectNodeUsageById(q);
    }














    @Override
    @Transactional(rollbackFor = Exception.class)
    public String importExtFields(List<SbtzExtImportDTO> rows, boolean onlyWhenSpecEmpty, boolean ignoreBlank) {
        if (rows == null || rows.isEmpty()) {
            return "未检测到数据行。";
        }

        // 1) 去掉无 id 的行
        List<SbtzExtImportDTO> valid = rows.stream()
                .filter(r -> r.getId() != null)
                .collect(Collectors.toList());

        // 2) 按 id 去重（保留列表最后一次出现的记录）
        Map<Long, SbtzExtImportDTO> latestById = new LinkedHashMap<>();
        for (SbtzExtImportDTO r : valid) {
            latestById.put(r.getId(), r);
        }

        int ok = 0, skip = 0, notFound = 0, fail = 0;
        StringBuilder sb = new StringBuilder();

        for (SbtzExtImportDTO dto : latestById.values()) {
            Long id = dto.getId();
            Sbtz old = sbtzMapper.selectSbtzById(id);
            if (old == null) {
                notFound++;
                sb.append("ID=").append(id).append(" 不存在；跳过。").append('\n');
                continue;
            }

            // 3) 构造“选择性更新”实体（只填要改的字段）
            Sbtz patch = new Sbtz();
            patch.setId(id);

            // ignoreBlank=true 时，仅对非空白的单元格赋值；否则允许覆盖为空串
            if (!ignoreBlank || StringUtils.isNotBlank(dto.getSpecification())) patch.setSpecification(trim(dto.getSpecification()));
            if (!ignoreBlank || StringUtils.isNotBlank(dto.getModel()))          patch.setModel(trim(dto.getModel()));
            if (!ignoreBlank || StringUtils.isNotBlank(dto.getMaterial()))       patch.setMaterial(trim(dto.getMaterial()));
            if (!ignoreBlank || StringUtils.isNotBlank(dto.getGrade()))          patch.setGrade(trim(dto.getGrade()));
            if (!ignoreBlank || StringUtils.isNotBlank(dto.getPower()))          patch.setPower(trim(dto.getPower()));
            if (!ignoreBlank || StringUtils.isNotBlank(dto.getBrand()))          patch.setBrand(trim(dto.getBrand()));
            if (!ignoreBlank || StringUtils.isNotBlank(dto.getManufacturer()))   patch.setManufacturer(trim(dto.getManufacturer()));

            // 如果全部是 null（在 ignoreBlank=true 且本行全空的情况），直接跳过
            if (allNullExt(patch)) {
                skip++;
                continue;
            }

            int n;
            if (onlyWhenSpecEmpty) {
                // 仅当库里 specification 为空/空串 才更新
                n = sbtzMapper.updateExtSelectiveWhenSpecEmpty(patch);
            } else {
                // 直接选择性更新
                n = sbtzMapper.updateExtSelective(patch);
            }

            if (n > 0) ok++;
            else {
                // 多半是条件未满足（如 onlyWhenSpecEmpty=true 且库里 specification 已有值）
                skip++;
            }
        }

        sb.append("导入完成：更新成功 ").append(ok).append(" 条，跳过 ").append(skip)
                .append(" 条，ID不存在 ").append(notFound).append(" 条，失败 ").append(fail).append(" 条。");

        return sb.toString();
    }

    private static boolean allNullExt(Sbtz p) {
        return p.getSpecification() == null
                && p.getModel() == null
                && p.getMaterial() == null
                && p.getGrade() == null
                && p.getPower() == null
                && p.getBrand() == null
                && p.getManufacturer() == null;
    }






    /*测试导入excel数据*/
//    @Override
//    @Transactional(rollbackFor = Exception.class)
//    public String importExcel(MultipartFile file) throws Exception {
//        if (file == null || file.isEmpty()) throw new ServiceException("上传文件为空");
//
//        // 1) 读取Excel
//        ExcelUtil<AssetImportRow> util = new ExcelUtil<>(AssetImportRow.class);
//        List<AssetImportRow> rows = util.importExcel(file.getInputStream());
//
//        if (rows == null || rows.isEmpty()) return "文件无数据";
//
//        // 2) 逐行处理：系统→母设备
//        int sysNew = 0, sysExist = 0, devNew = 0, devUpd = 0, skip = 0;
//
//        // 缓存：系统名 -> 系统ID
//        Map<String, Long> sysIdCache = new HashMap<>();
//
//        // 记住“最近一次非空的系统名称”
//        String lastSysName = null;
//        for (AssetImportRow r : rows) {
//            if (r == null) { skip++; continue; }
//            String sysName = trim(r.getSystemName());
//            String devName = trim(r.getDeviceName());
////            if (StringUtils.isEmpty(sysName)) { skip++; continue; }
//
//            // 若本行系统名为空，则沿用上一次非空的系统名；如果之前也没有，就跳过
//            if (sysName == null) {
//                if (lastSysName != null) {
//                    sysName = lastSysName;    // ← 填充为上一条的系统名
//                } else {
//                    skip++;                   // 文件一开始就空系统名的行，依然跳过
//                    continue;
//                }
//            } else {
//                lastSysName = sysName;        // 更新“最近一次非空的系统名”
//            }
//            // a) 系统（parent=0, nodeType=SYSTEM）
//            Long sysId = sysIdCache.get(sysName);
//            if (sysId == null) {
//                sysId = ensureSystem(sysName);
//                if (sysId < 0) { // 已存在
//                    sysId = -sysId;
//                    sysExist++;
//                } else {
//                    sysNew++;
//                }
//                sysIdCache.put(sysName, sysId);
//            }
//
//            // b) 无设备名则跳过（只建系统）
//            if (StringUtils.isEmpty(devName)) continue;
//
//            // c) 母设备（nodeType=MACHINE）
//            boolean updated = upsertDeviceUnderSystem(sysId, devName, r);
//            if (updated) devUpd++; else devNew++;
//        }
//
//        return String.format("导入完成：新增系统 %d 个，已存在系统 %d 个；新增设备 %d 台，更新设备 %d 台；跳过 %d 行。",
//                sysNew, sysExist, devNew, devUpd, skip);
//    }

@Override
@Transactional(rollbackFor = Exception.class)
public String importExcel(MultipartFile file) throws Exception {
    if (file == null || file.isEmpty()) throw new ServiceException("上传文件为空");

    ExcelUtil<AssetImportRow> util = new ExcelUtil<>(AssetImportRow.class);
    List<AssetImportRow> rows = util.importExcel(file.getInputStream());
    if (rows == null || rows.isEmpty()) return "文件无数据";

    int sysNew = 0, sysExist = 0, devNew = 0, devUpd = 0, skip = 0;

    // ★ 一次性预加载：根层系统（parent_id=0）
    List<Sbtz> rootSystems = sbtzMapper.selectAllRootSystems();
    Map<String, Long> sysIdByName = new HashMap<>(rootSystems.size() * 2);
    Map<String, Long> sysIdByCode = new HashMap<>(rootSystems.size() * 2);
    for (Sbtz s : rootSystems) {
        if (org.apache.commons.lang3.StringUtils.isNotBlank(s.getName())) {
            sysIdByName.put(norm(s.getName()), s.getId());
        }
        if (org.apache.commons.lang3.StringUtils.isNotBlank(s.getCode())) {
            sysIdByCode.put(norm(s.getCode()), s.getId());
        }
    }

    String lastSysName = null;

    for (AssetImportRow r : rows) {
        if (r == null) { skip++; continue; }

        String sysName = trimToNull(r.getSystemName());
        String devName = trimToNull(r.getDeviceName());

        // 沿用上一条非空系统名
        if (sysName == null) {
            if (lastSysName == null) { skip++; continue; }
            sysName = lastSysName;
        } else {
            lastSysName = sysName;
        }

        // ★ 先在缓存中按“系统名/系统编码”找
        Long sysId = sysIdByName.get(norm(sysName));
//        if (sysId == null && org.apache.commons.lang3.StringUtils.isNotBlank(r.getSystemCode())) {
//            sysId = sysIdByCode.get(norm(r.getSystemCode()));
//        }

        if (sysId == null) {
            // ★ 缓存未命中：创建系统，并把新系统放进缓存
            Sbtz sys = buildSystem(sysName);
            sbtzMapper.insertExcelNode(sys);

            String path = "/" + sys.getId() + "/";
            sbtzMapper.updatePathLevel(sys.getId(), "", path, 1);
            sbtzMapper.insertRelSelf(sys.getId());

            sysId = sys.getId();
            sysNew++;

            sysIdByName.put(norm(sysName), sysId);
            if (org.apache.commons.lang3.StringUtils.isNotBlank(sys.getCode())) {
                sysIdByCode.put(norm(sys.getCode()), sysId);
            }
        } else {
            sysExist++;
        }

        // 没有设备名：只建系统
        if (org.apache.commons.lang3.StringUtils.isBlank(devName)) continue;

        // 设备：存在则更新，否则新增
        boolean updated = upsertDeviceUnderSystem(sysId, devName, r);
        if (updated) devUpd++; else devNew++;
    }

    return String.format(
            "导入完成：新增系统 %d 个，已存在系统 %d 个；新增设备 %d 台，更新设备 %d 台；跳过 %d 行。",
            sysNew, sysExist, devNew, devUpd, skip
    );
}

    /* ========== 辅助方法 ========== */

    private static String norm(String s) {
        return s == null ? null : s.trim().toLowerCase();
    }
    private static String trimToNull(String s) {
        String x = (s == null ? null : s.trim());
        return (x == null || x.isEmpty()) ? null : x;
    }

    /** 原 ensureSystem 改成“只负责构建实体”，插入/闭包外面做，便于把新系统回填进缓存 */
    private Sbtz buildSystem(String sysName) {
        Sbtz n = new Sbtz();
        n.setName(sysName);
        n.setNodeType("SYSTEM");
        n.setCode(genUniqueCode("SYS", sysName));
        n.setParentId(0L);
        n.setAncestors("");
        n.setLevel(1);
        n.setPath("");
        n.setOrderNum(0);
        n.setStatus(0);
        n.setDelFlag(0);
        n.setRemark("");
        n.setSpecification("");
        n.setModel("");
        n.setMaterial("");
        n.setPower("");
        n.setBrand("");
        n.setManufacturer("");
        n.setIsSpecial(0);
        n.setDeptId(0);

        n.setMokuai("");
        n.setArea("");
        n.setBeType("");
        n.setDesignTemp("");
        n.setDesignPress("");
        n.setMotorPower("");
        n.setMixingType("");
        n.setUnit("");
        n.setQuantity(0);
        n.setAmount(new java.math.BigDecimal("0"));
        return n;
    }

    /** 如果系统已存在：返回其ID的相反数；否则插入并返回新ID */
    private Long ensureSystem(String sysName) {
        Long existId = sbtzMapper.selectIdByNameAndParent(sysName, 0L);
        if (existId != null) return -existId;

        Sbtz n = new Sbtz();
        n.setName(sysName);
        n.setNodeType("SYSTEM");
        n.setCode(genUniqueCode("SYS", sysName));
        n.setParentId(0L);
        n.setAncestors("");
        n.setLevel(1);
        n.setPath(""); // 插入后回填
        n.setOrderNum(0);
        n.setStatus(0);
        n.setDelFlag(0);
        n.setRemark("");
        n.setSpecification("");
        n.setModel("");
        n.setMaterial("");
        n.setPower("");
        n.setBrand("");
        n.setManufacturer("");
        n.setIsSpecial(0);
        n.setDeptId(0);
        n.setMokuai("");
        n.setArea("");
        n.setBeType("");
        n.setDesignTemp("");
        n.setDesignPress("");
        n.setMotorPower("");
        n.setMixingType("");
        n.setUnit("");
        n.setQuantity(0);
        n.setAmount(new BigDecimal("0"));

        sbtzMapper.insertExcelNode(n);
        // 回填 path/level/ancestors
        String path = "/" + n.getId() + "/";
        sbtzMapper.updatePathLevel(n.getId(), "", path, 1);

        // 维护闭包表
        sbtzMapper.insertRelSelf(n.getId());               // (self, self, 0)

        return n.getId();
    }

    /** 存在则更新，不存在则新增；返回 true=更新, false=新增 */
    private boolean upsertDeviceUnderSystem(Long sysId, String devName, AssetImportRow r) {
        Long existId = sbtzMapper.selectIdByNameAndParent(devName, sysId);
        if (existId != null) {
            // 更新关键属性（可按需扩展）
            Sbtz n = new Sbtz();
            n.setId(existId);
            n.setName(devName);
            n.setRemark(trim(r.getRemark()));
            n.setSpecification(trim(r.getSpecification()));
            n.setMaterial(trim(r.getMaterial()));
            n.setDesignTemp(trim(r.getDesignTemp()));
            n.setDesignPress(trim(r.getDesignPress()));
            n.setMotorPower(trim(r.getMotorPower()));
            n.setMixingType(trim(r.getMixingType()));
            n.setUnit(trim(r.getUnit()));
            n.setQuantity(r.getQuantity() == null ? 0 : r.getQuantity());
            n.setAmount(r.getAmount() == null ? BigDecimal.ZERO : r.getAmount());
            n.setMokuai(trim(r.getMokuai()));
            n.setArea(trim(r.getArea()));
            n.setBeType(trim(r.getBeType()));
            sbtzMapper.updatePartFields(n);
            return true;
        }

        // 新增
        Sbtz n = new Sbtz();
        n.setName(devName);
        n.setNodeType("MACHINE");
        n.setCode(StringUtils.isNotEmpty(r.getCode()) ? r.getCode() : genUniqueCode("DEV", devName));
        n.setParentId(sysId);
        n.setOrderNum(0);
        n.setStatus(0);
        n.setDelFlag(0);
        n.setRemark(trim(r.getRemark()));
        n.setSpecification(trim(r.getSpecification()));
        n.setModel(""); // 如表里没有就留空
        n.setMaterial(trim(r.getMaterial()));
        n.setPower(""); // 可用 motor_power 也行，这里分开存
        n.setBrand("");
        n.setManufacturer("");
        n.setIsSpecial(0);
        n.setDeptId(0);

        n.setMokuai(trim(r.getMokuai()));
        n.setArea(trim(r.getArea()));
        n.setBeType(trim(r.getBeType()));
        n.setDesignTemp(trim(r.getDesignTemp()));
        n.setDesignPress(trim(r.getDesignPress()));
        n.setMotorPower(trim(r.getMotorPower()));
        n.setMixingType(trim(r.getMixingType()));
        n.setUnit(trim(r.getUnit()));
        n.setQuantity(r.getQuantity() == null ? 1 : r.getQuantity());
        n.setAmount(r.getAmount() == null ? BigDecimal.ZERO : r.getAmount());

        // ancestors/level/path 暂留，插入后回填
        sbtzMapper.insertExcelNode(n);

        // 回填：读取父节点信息（只需父path/level/ancestors即可）
        Sbtz p = sbtzMapper.selectById(sysId);
        String ancestors = StringUtils.isEmpty(p.getAncestors()) ? String.valueOf(sysId) : p.getAncestors() + "," + sysId;
        int level = (p.getLevel() == null ? 1 : p.getLevel()) + 1;
        String path = (StringUtils.isEmpty(p.getPath()) ? "/" : p.getPath()) + n.getId() + "/";

        sbtzMapper.updatePathLevel(n.getId(), ancestors, path, level);

        // 闭包表：
        sbtzMapper.insertRelSelf(n.getId());                 // (n, n, 0)
        sbtzMapper.insertRelByParent(sysId, n.getId());      // 继承父所有祖先 + 父本身

        return false;
    }

    private static String trim(String s) { return s == null ? "" : s.trim(); }

    /** 仅用英文数字+大写；中文名会退化到随机尾码，避免引入拼音依赖 */
    private String genUniqueCode(String prefix, String name) {
        String base = (name == null ? "" : name).replaceAll("[^A-Za-z0-9]", "").toUpperCase();
        if (base.length() == 0) base = "X";
        if (base.length() > 10) base = base.substring(0, 10);
        // 时间+随机，避免唯一键冲突
        String tail = Long.toString(System.currentTimeMillis(), 36).toUpperCase();
        return prefix + "-" + base + "-" + tail.substring(tail.length()-5);
    }
    /*测试导入excel数据 end*/






    /** 复用 children() 的 label 规则： name（code） */
    private static String buildLabel(String name, String code) {
        if (code == null || code.isEmpty()) return String.valueOf(name);
        return String.valueOf(name) + "（" + code + "）";
    }

    @Override
    public List<NodeOptionVO> getPathByLeaf(Long leafId) {
        if (leafId == null || leafId <= 0) return Collections.emptyList();

        List<NodeOptionVO> stack = new ArrayList<>(8);
        Long cur = leafId;
        int guard = 0; // 防御环
        while (cur != null && cur > 0 && guard++ < 100) {
            Sbtz n = sbtzMapper.selectById(cur);
            if (n == null) break;

            stack.add(new NodeOptionVO(
                    n.getId(),
                    buildLabel(n.getName(), n.getCode()),
                    null,            // leaf 字段这里不需要，传 null
                    n.getNodeType()  // 可选；前端回显不使用也没关系
            ));
            cur = n.getParentId(); // 一路向上
        }
        // stack 现在是 叶→根，反转得到 根→叶，刚好给级联 v-model
        Collections.reverse(stack);
        return stack;
    }


    @Override
    public boolean checkBeTypeFirst(Map<String, Object> params) {
        Integer count = sbtzMapper.checkBeTypeFirst(params);
        return count != null && count > 0;
    }








    @Override
    @Transactional(rollbackFor = Exception.class)
    public List<Long> createRoute(RouteCreateDTO dto, String operUser) {
        if (dto == null || dto.getLayers() == null || dto.getLayers().isEmpty()) {
            throw new ServiceException("没有需要创建的层");
        }

        Long parentId = (dto.getStartParentId() == null ? 0L : dto.getStartParentId());
        String parentType;
        String parentPath = "/";
        String parentAncestors = "";
        int parentLevel = 0;

        if (parentId != 0L) {
            Sbtz parent = sbtzMapper.getById(parentId);
            if (parent == null) throw new ServiceException("父节点不存在：" + parentId);
            parentType = parent.getNodeType();
            parentPath = StringUtils.defaultIfBlank(parent.getPath(), "/");
            parentAncestors = StringUtils.defaultString(parent.getAncestors());
            parentLevel = parent.getLevel();
        } else {
            parentType = "ROOT";
        }

        List<Long> createdIds = new ArrayList<>();

        // 处理 systemInfo（原样保留）
        if ((parentId == 0L || "ROOT".equals(parentType)) && dto.getSystemInfo() != null) {
            RouteCreateDTO.SystemInfo sys = dto.getSystemInfo();
            if (StringUtils.isNotBlank(sys.getName()) && StringUtils.isNotBlank(sys.getCode())) {

                Sbtz rootInfo = sbtzMapper.selectRootInfo(sys.getCode());

                if (rootInfo != null) {
                    // 如果找到现有根节点，复用其数据
                    Long sysId = rootInfo.getId();
                    parentType = rootInfo.getNodeType();
                    parentPath = rootInfo.getPath();
                    parentAncestors = rootInfo.getAncestors();
                    parentLevel = rootInfo.getLevel();
                    createdIds.add(sysId);
                }else{
                    Sbtz sysNode = new Sbtz();
                    sysNode.setNodeType("SYSTEM");
                    sysNode.setName(sys.getName());
                    sysNode.setCode(ensureUniqueCodeCreate(sys.getCode(), false)); // 系统节点 code 仍用原逻辑
                    sysNode.setIsKey(sys.getIsKey());
                    sysNode.setParentId(0L);
                    sysNode.setLevel(1);
                    sysNode.setPath("/");
                    sysNode.setAncestors("");
                    sysNode.setStatus(0);
                    sysNode.setDelFlag(0);
                    sysNode.setRemark("由母设备创建时自动生成系统节点");
                    sysNode.setCreateBy(operUser);
                    sysNode.setUpdateBy(operUser);
                    sysNode.setCreateTime(new Date());
                    sysNode.setUpdateTime(new Date());

                    sbtzMapper.insertNode(sysNode);

                    Long sysId = sysNode.getId();
                    sbtzMapper.updatePath(sysId, "/" + sysId + "/");

                    parentId = sysId;
                    parentType = "SYSTEM";
                    parentLevel = 1;
                    parentPath = "/" + sysId + "/";
                    parentAncestors = sysNode.getAncestors();
                    createdIds.add(sysId);
                }
            }
        }

        // 递归处理每个顶层 layer
        boolean reuse = Boolean.TRUE.equals(dto.getReuseCode());
        for (RouteCreateDTO.Layer layer : dto.getLayers()) {
            createNode(layer, parentId, parentType, parentPath, parentAncestors, parentLevel, reuse, operUser, createdIds);
        }

        return createdIds;
    }

    /**
     * 递归创建节点及其子节点
     * @param layer 当前层数据
     * @param parentId 父节点 ID
     * @param parentType 父节点类型
     * @param parentPath 父路径
     * @param parentAncestors 父祖先链
     * @param parentLevel 父层级
     * @param reuse 是否复用 code
     * @param operUser 操作用户
     * @param createdIds 收集创建的 ID 列表
     */
    private void createNode(RouteCreateDTO.Layer layer, Long parentId, String parentType, String parentPath,
                            String parentAncestors, int parentLevel, boolean reuse, String operUser, List<Long> createdIds) {
        if (StringUtils.isBlank(layer.getNodeType()) || StringUtils.isBlank(layer.getName())) {
            throw new ServiceException("类型与名称为必填");
        }

        String sha256Hash = processAssetHash(
                layer.getSpecification(),
                layer.getModel(),
                layer.getMaterial(),
                layer.getBrand(),
                layer.getManufacturer()
        );

        String childType = layer.getNodeType().trim().toUpperCase();
        String parentName = layer.getParentName();
        if (StringUtils.isNotBlank(parentName)) {
            Sbtz parent = sbtzMapper.selectLastIdByName(parentName);
            if (parent == null) {
                throw new ServiceException("父节点名称不存在：" + parentName);
            }
            parentId = parent.getId();
            parentType = parent.getNodeType();

        }
        assertEdgeAllowed(parentType, childType);

        // 生成 code：如果为空，根据 nodeType 生成递增 code
        String finalCode = generateNextCodeByType(childType);

        Sbtz node = new Sbtz();
        node.setName(layer.getName());
        node.setNodeType(childType);
        node.setCode(finalCode);
        node.setParentId(parentId);
        node.setLevel(parentLevel + 1);
        node.setAncestors(buildAncestors(parentAncestors, parentId));
        node.setPath(parentPath);
        node.setOrderNum(0);
        node.setStatus(layer.getStatus());
        node.setDelFlag(0);
        node.setRemark(layer.getRemark());
        node.setCreateBy(operUser);
        node.setUpdateBy(operUser);
        node.setCreateTime(new Date());
        // 其他字段（原样保留）
        node.setSpecification(layer.getSpecification());
        node.setModel(layer.getModel());
        node.setMaterial(layer.getMaterial());
        node.setPower(layer.getPower());
        node.setBrand(layer.getBrand());
        node.setManufacturer(layer.getManufacturer());
        node.setIsSpecial(layer.getIsSpecial());
        node.setDeptId(layer.getDeptId());
        node.setMokuai(layer.getMokuai());
        node.setArea(layer.getArea());
        node.setBeType(layer.getBeType());
        node.setDesignTemp(layer.getDesignTemp());
        node.setDesignPress(layer.getDesignPress());
        node.setMotorPower(layer.getMotorPower());
        node.setMixingType(layer.getMixingType());
        node.setUnit(layer.getUnit());
        node.setQuantity(layer.getQuantity());
        node.setAmount(layer.getAmount());

        sbtzMapper.insertNode(node);
        Long id = node.getId();
        String fullPath = safePath(parentPath) + id + "/";
        sbtzMapper.updatePath(id, fullPath);
        sbtzMapper.insertClosureForNewNode(parentId, id);

        // 移除备件逻辑：无 spareRelations 和 spareCustom 处理

        // 其他逻辑（原样保留）
        if (layer.getDeptId() != null) {
            changeUsage(id, layer.getStatus(), (long) layer.getDeptId(), operUser, "");
        }
        if (StringUtils.isNotBlank(layer.getBeType())) { // 移除 spareCustom 判断
            Long minId = sbtzMapper.selectMinIdByBeType(layer.getBeType());
            if (minId != null) {
                sbtzMapper.updateBeTypeFirst(minId, 1);
            } else {
                sbtzMapper.updateBeTypeFirst(id, 1);
            }
        }

        createdIds.add(id);

        // 递归处理 children
        if (layer.getChildren() != null && !layer.getChildren().isEmpty()) {
            for (RouteCreateDTO.Layer child : layer.getChildren()) {
                createNode(child, id, childType, fullPath, node.getAncestors(), node.getLevel(), reuse, operUser, createdIds);
            }
        }
    }


    /**
     * 处理资产，生成 SHA-256 哈希值并返回
     */
    @Override
    public String processAssetHash(String specification, String model, String material, String brand, String manufacturer) {
        // 将多个字段组合成一个字符串
        String combinedString = specification + "_" + model + "_" + material + "_"  + brand + "_" + manufacturer;

        // 生成 SHA-256 哈希值
        String sha256Hash = HashUtil.generateSHA256Hash(combinedString);

        // 返回生成的 SHA-256 哈希值
        return sha256Hash;
    }

    /**
     * 根据 nodeType 生成下一个递增 code（如 MACHINE0000001）
     * @param nodeType 节点类型
     * @return 新 code
     */
    private String generateNextCodeByType(String nodeType) {
        String maxCode = sbtzMapper.getMaxCodeByType(nodeType); // 假设 mapper 有此方法，返回如 "MACHINE0000005"
        long nextNum = 1L;
        if (StringUtils.isNotBlank(maxCode)) {
            // 解析数字部分（如从 "MACHINE0000005" 提取 5）
            String numStr = maxCode.replaceAll("^" + Pattern.quote(nodeType), "").replaceAll("^0+", "");
            if (StringUtils.isNotBlank(numStr)) {
                nextNum = Long.parseLong(numStr) + 1;
            }
        }
        // 格式化：nodeType + 7位0填充数字
        String numFormatted = String.format("%07d", nextNum);
        String newCode = nodeType + numFormatted;

        // 确保唯一：如果已存在，继续 +1（递归或循环）
        while (sbtzMapper.existsByCode(newCode)) {
            nextNum++;
            numFormatted = String.format("%07d", nextNum);
            newCode = nodeType + numFormatted;
        }
        return newCode;
    }






}
